import random
import sqlite3
import time
from typing import Literal

from datetime import datetime, timedelta
from .schemas.alarms import Alarm, AlarmDescription, CURRENT_TIME_POINT


class AlarmDB:
    __slots__ = ['_connection']

    def __init__(self, name: str):
        self._connection = sqlite3.connect(name)
        self._configure_db()

    def clear(self):
        self._connection.cursor()\
            .execute('DROP TABLE IF EXISTS ALARMS_LOG')

        self._connection.cursor() \
            .execute('DROP TABLE IF EXISTS ALARMS_ROLE')

        self._connection.commit()

        self._configure_db()

    def _configure_db(self):
        cursor = self._connection.cursor()

        cursor.execute(
            "CREATE TABLE IF NOT EXISTS ALARMS_LOG (" +
            "code INTEGER NOT NULL, " +
            "value NUMERIC NOT NULL, " +
            "end_value NUMERIC, " +
            "state NUMERIC NOT NULL, " +
            "time NUMERIC NOT NULL, " +
            "end_time NUMERIC, " +
            "aid INTEGER PRIMARY KEY AUTOINCREMENT" +
            ")"
        )

        cursor.execute(
            "CREATE TABLE IF NOT EXISTS ALARMS_ROLE (" +
            "aid INTEGER PRIMARY KEY, " +
            "user NUMERIC NOT NULL, " +
            "server NUMERIC NOT NULL" +
            ")"
        )

        cursor.execute(
            "CREATE TABLE IF NOT EXISTS ALARMS_TIME (" +
            "aid INTEGER PRIMARY KEY, " +
            "offset NUMERIC NOT NULL, " +
            "point_id INTEGER NOT NULL, " +
            "end_offset NUMERIC, " +
            "end_point_id INTEGER" +
            ")"
        )

        cursor.execute(
            "CREATE TABLE IF NOT EXISTS ALARMS_POINTS (" +
            "point_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "timestamp NUMERIC" +
            ")"
        )

        self._connection.commit()

    def delete_log_alarm(self, aids: list):
        cursor = self._connection.cursor()
        cursor.executemany(
            f"DELETE FROM ALARMS_LOG WHERE aid = ?",
            [
                (aid, )
                for aid in aids
            ]
        )
        self._connection.commit()

    def close_power_alarms(self):
        end_time = int(datetime.now().timestamp())
        end_time_offset = int(time.monotonic())
        end_time_point = CURRENT_TIME_POINT[0]

        cursor = self._connection.cursor()
        unclosed = cursor.execute("SELECT aid FROM ALARMS_LOG WHERE end_value IS NULL and code = 108").fetchmany()

        for alarm in unclosed:
            aid = alarm[0]
            cursor.execute(f"UPDATE ALARMS_LOG SET state = 0, end_value = 0, end_time = {end_time} WHERE aid = {aid}")
            cursor.execute(f"UPDATE ALARMS_TIME SET end_offset = {end_time_offset}, end_point_id = {end_time_point} WHERE aid = {aid}")


        self._connection.commit()


    def make_current_time_point(self):
        cursor = self._connection.cursor()
        cursor.executemany(
            "INSERT OR REPLACE INTO ALARMS_POINTS(point_id, timestamp) VALUES(?,?)",
            [
                (None, None)
            ]
        )
        self._connection.commit()
        point_id = self._connection.execute("SELECT last_insert_rowid()").fetchone()[0]

        CURRENT_TIME_POINT[0] = point_id

    def update_current_time_point(self):
        current_offset = int(time.monotonic())
        current_timestamp = int(datetime.now().timestamp())
        cursor = self._connection.cursor()
        cursor.executemany(
            "INSERT OR REPLACE INTO ALARMS_POINTS(point_id, timestamp) VALUES(?,?)",
            [
                (CURRENT_TIME_POINT[0], current_timestamp - current_offset),
            ]
        )
        self._connection.commit()

    def get_time_point_timestamp(self, point_id) -> int | None:
        if point_id is None:
            return None

        try:
            cursor = self._connection.cursor()
            return cursor.execute(f'SELECT timestamp FROM ALARMS_POINTS WHERE point_id = {point_id}').fetchone()[0]
        except Exception:
            return None


    def log_alarms(self, alarms: Alarm):
        try:
            cursor = self._connection.cursor()
            cursor.executemany(
                "INSERT OR REPLACE INTO ALARMS_LOG(aid, code, value, end_value, state, time, end_time) VALUES(?,?,?,?,?,?,?)",
                [
                    (alarm.aid, alarm.code, alarm.value, alarms.end_value, alarm.state, alarm.time, alarm.end_time)
                    for alarm in [alarms]
                ]
            )

            self._connection.commit()
            aid = self._connection.execute("SELECT last_insert_rowid()").fetchone()[0]
            alarms.aid = aid

            self._connection.cursor().executemany(
                "INSERT OR REPLACE INTO ALARMS_ROLE(aid, user, server) VALUES(?,?,?)",
                [
                    (alarm.aid, alarm.user, alarm.server)
                    for alarm in [alarms]
                ]
            )
            self._connection.commit()

            self._connection.cursor().executemany(
                "INSERT OR REPLACE INTO ALARMS_TIME(aid, offset, point_id, end_offset, end_point_id) VALUES(?,?,?,?,?)",
                [
                    (alarm.aid, alarm.time_offset, alarm.time_point, alarm.end_time_offset, alarm.end_time_point)
                    for alarm in [alarms]
                ]
            )
            self._connection.commit()

            return aid
        except Exception as e:
            return None

    def update_role_state(self, aid: int, user: bool, server: bool):
        self._connection.cursor().executemany(
            "INSERT OR REPLACE INTO ALARMS_ROLE(aid, user, server) VALUES(?,?,?)",
            [(aid, user, server)]
        )
        self._connection.commit()

    def update_alarms_description(self, descriptions: list[AlarmDescription]):
        self._reacreate_descriptions_table()

        cursor = self._connection.cursor()
        sql = "INSERT INTO ALARMS_DESCRIPTION(code, name, category, subcategory, description_en, description_ru, is_warning) " +\
              "VALUES(?,?,?,?,?,?,?)"

        cursor.executemany(
            sql,
            [(d.code, d.name, d.group, d.subgroup, d.description_en, d.description_ru, d.is_warning) for d in descriptions]
        )

        self._connection.commit()

    def get_newest_oldest_history_date(self, role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        asc = self._connection.cursor().execute(
            "SELECT ALARMS_LOG.time FROM ALARMS_LOG " +
            self._get_user_filter(role) +
            "ORDER BY time ASC LIMIT 1"
        ).fetchall()

        desc = self._connection.cursor().execute(
            "SELECT ALARMS_LOG.time FROM ALARMS_LOG " +
            self._get_user_filter(role) +
            "ORDER BY time DESC LIMIT 1"
        ).fetchall()

        count = self._connection.cursor().execute(
            "SELECT COUNT(*) FROM ALARMS_LOG " +
            self._get_user_filter(role)
        ).fetchall()

        # sql = "SELECT * FROM (SELECT ALARMS_LOG.time FROM ALARMS_LOG ORDER BY time ASC LIMIT 1) " \
        #       "UNION ALL SELECT * FROM (SELECT ALARMS_LOG.time FROM ALARMS_LOG ORDER BY time DESC LIMIT 1) " \
        #       "UNION ALL SELECT COUNT(*) FROM ALARMS_LOG"
        # cursor = self._connection.cursor()
        # cursor.execute(sql)
        # result = cursor.fetchall()
        return [asc[0], desc[0], count[0]]

    def get_not_exist_group_count(self, group_count_list):
        category = ['DSP', 'Modem', 'Optic', 'Power', 'RM', 'SFP', 'SM', 'Sens', 'GPS', 'Info']
        for group in group_count_list:
            if group[0] in category:
                category.remove(group[0])
        for group in category:
            tup = (group, 0, 0)
            group_count_list.append(tup)
        return group_count_list

    def get_not_choosed_group_count(self, filter, request, role: Literal['adjuster', 'user', 'server'] = 'adjuster', is_warning = False):
        sql = "SELECT * FROM (SELECT ALARMS_DESCRIPTION.category AS [group], COUNT(*) " +\
            "FROM ALARMS_LOG INNER JOIN ALARMS_DESCRIPTION ON ALARMS_LOG.code = ALARMS_DESCRIPTION.code " +\
            self._get_user_join(role)

        sql += f"WHERE {self._get_user_condition(role)} AND (ALARMS_DESCRIPTION.is_warning = {int(is_warning)}) " if filter is None else f"WHERE {self._get_user_condition(role)} AND (ALARMS_DESCRIPTION.is_warning = {int(is_warning)}) AND {filter.replace('[code]', 'ALARMS_LOG.code').replace('[aid]', 'ALARMS_LOG.aid')} "
        sql += "GROUP BY [category]) " \
               "UNION ALL "
        sql += "SELECT * FROM (SELECT ALARMS_DESCRIPTION.category AS [group], COUNT(*) FROM ALARMS_LOG INNER JOIN " +\
               "ALARMS_DESCRIPTION ON ALARMS_LOG.code = ALARMS_DESCRIPTION.code " + \
               self._get_user_join(role)

        sql += f"WHERE {self._get_user_condition(role)} AND {request.start_date.timestamp()} <= [time] AND [time] <= {request.end_date.timestamp()} AND (ALARMS_DESCRIPTION.is_warning = {int(is_warning)}) " \
               f"GROUP BY [category])"
        # print('2. THIS IS NOT CHOOSED FILTER - ', sql)

        cursor = self._connection.cursor()
        cursor.execute(sql)
        res = cursor.fetchall()
        not_choosed = self.get_not_exist_group_count(res)
        # print('3. THIS IS GET NOT CHOOSE GROUP COUNT - ', not_choosed)
        return not_choosed

    def get_alarms_count_by_group(self, filter, request, role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        not_choosed_group_warnings = self.get_not_choosed_group_count(filter, request, role, True)
        not_choosed_group_alarms = self.get_not_choosed_group_count(filter, request, role, False)

        not_choosed_group = {}
        for alarm in not_choosed_group_alarms:
            not_choosed_group[alarm[0]] = (0, alarm[1])

        for alarm in not_choosed_group_warnings:
            if alarm[0] in not_choosed_group:
                not_choosed_group[alarm[0]] = (alarm[1], not_choosed_group[alarm[0]][1])
            else:
                not_choosed_group[alarm[0]] = (alarm[1], 0)

        # print('4. THIS IS GET CURRENT ALARMS COUNT BY GROUP AFTER NOT CHOOSE - ', alarms_count_by_group)

        return not_choosed_group

    def get_alarms_count(self, filter, role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        sql = "SELECT ALARMS_DESCRIPTION.category AS [group], COUNT(*), ALARMS_DESCRIPTION.is_warning AS [is_warning], " + \
            "IIF(ALARMS_POINTS.timestamp IS NOT NULL AND ALARMS_TIME.offset IS NOT NULL, ALARMS_POINTS.timestamp + ALARMS_TIME.offset, ALARMS_LOG.time) as [time], " + \
            "IIF(ALARMS_POINTS_END.timestamp IS NOT NULL AND ALARMS_TIME.end_offset IS NOT NULL, ALARMS_POINTS_END.timestamp + ALARMS_TIME.end_offset, ALARMS_LOG.end_time) as [end_time] " + \
            "FROM ALARMS_LOG INNER JOIN ALARMS_DESCRIPTION ON ALARMS_LOG.code = ALARMS_DESCRIPTION.code " + \
            'LEFT JOIN ALARMS_TIME ON ALARMS_LOG.aid = ALARMS_TIME.aid ' + \
            'LEFT JOIN ALARMS_POINTS ON ALARMS_TIME.point_id = ALARMS_POINTS.point_id ' + \
            'LEFT JOIN ALARMS_POINTS as ALARMS_POINTS_END ON ALARMS_TIME.end_point_id = ALARMS_POINTS_END.point_id ' +\
            self._get_user_join(role)

        sql += f"WHERE {self._get_user_condition(role)} " if filter is None else f"WHERE {self._get_user_condition(role)} AND {filter.replace('[code]', 'ALARMS_LOG.code').replace('[aid]', 'ALARMS_LOG.aid')} "
        # sql += "and [state] = 1"
        # print('THIS IS ALL ALARMS COUNT WITH FILTER - ', sql)
        cursor = self._connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchone()
        count = result[1]
        return count

    def get_alarms_filtered(self, description_language, filter: str = None, order_by: str = None, offset=0, count=8, role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        # print('THIS IS EMPTY FILTER - ', filter)
        sql = "SELECT ALARMS_LOG.code AS [index], ALARMS_LOG.aid as [aid], [value], [end_value], [state], " +\
            "IIF(ALARMS_POINTS.timestamp IS NOT NULL AND ALARMS_TIME.offset IS NOT NULL, ALARMS_POINTS.timestamp + ALARMS_TIME.offset, ALARMS_LOG.time) as [time], " + \
            "IIF(ALARMS_POINTS_END.timestamp IS NOT NULL AND ALARMS_TIME.end_offset IS NOT NULL, ALARMS_POINTS_END.timestamp + ALARMS_TIME.end_offset, ALARMS_LOG.end_time) as [end_time], " + \
            "ALARMS_DESCRIPTION.name AS [name], ALARMS_DESCRIPTION.category AS [group], " \
            "ALARMS_DESCRIPTION.subcategory AS [subgroup], ALARMS_DESCRIPTION.is_warning AS [is_warning], " \

        if description_language == 'en':
            sql += "ALARMS_DESCRIPTION.description_en AS [description_en] "
        else:
            sql += "ALARMS_DESCRIPTION.description_ru AS [description_ru] "

        sql += "FROM ALARMS_LOG INNER JOIN ALARMS_DESCRIPTION ON ALARMS_LOG.code = ALARMS_DESCRIPTION.code "
        sql += 'LEFT JOIN ALARMS_TIME ON ALARMS_LOG.aid = ALARMS_TIME.aid '
        sql += 'LEFT JOIN ALARMS_POINTS ON ALARMS_TIME.point_id = ALARMS_POINTS.point_id '
        sql += 'LEFT JOIN ALARMS_POINTS as ALARMS_POINTS_END ON ALARMS_TIME.end_point_id = ALARMS_POINTS_END.point_id '
        sql += self._get_user_join(role)

        # print('THIS IS FILTER - ', filter)
        sql += f"WHERE {self._get_user_condition(role)} " if filter is None else f"WHERE {self._get_user_condition(role)} AND {filter.replace('[code]', '[index]').replace('[aid]', 'ALARMS_LOG.aid')} "

        sql += "" if order_by is None else f"ORDER BY {order_by} "
        sql += f"LIMIT {count} "
        sql += f"OFFSET {offset} "
        # print(sql)
        cursor = self._connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchmany(count)
        return result

    def _reacreate_descriptions_table(self):
        cursor = self._connection.cursor()
        cursor.execute("DROP TABLE IF EXISTS ALARMS_DESCRIPTION")
        cursor.execute(
            "CREATE TABLE IF NOT EXISTS ALARMS_DESCRIPTION (" +
            "code INTEGER NOT NULL PRIMARY KEY, " +
            "name TEXT NOT NULL, " +
            "category TEXT NOT NULL, " +
            "subcategory TEXT, " +
            "description_en TEXT, " +
            "description_ru TEXT," +
            "is_warning NUMERIC NOT NULL" +
            ")"
        )
        self._connection.commit()

    @staticmethod
    def _get_user_filter(role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        return {
            'adjuster': '',
            'user': 'LEFT JOIN ALARMS_ROLE ON ALARMS_LOG.aid = ALARMS_ROLE.aid WHERE (ALARMS_ROLE.user = 1 OR ALARMS_ROLE.user is NULL) ',
            'server': 'LEFT JOIN ALARMS_ROLE ON ALARMS_LOG.aid = ALARMS_ROLE.aid WHERE (ALARMS_ROLE.server = 1 OR ALARMS_ROLE.server is NULL) '
        }[role]

    @staticmethod
    def _get_user_join(role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        return {
            'adjuster': '',
            'user': 'LEFT JOIN ALARMS_ROLE ON ALARMS_LOG.aid = ALARMS_ROLE.aid ',
            'server': 'LEFT JOIN ALARMS_ROLE ON ALARMS_LOG.aid = ALARMS_ROLE.aid '
        }[role]

    @staticmethod
    def _get_user_condition(role: Literal['adjuster', 'user', 'server'] = 'adjuster'):
        return {
            'adjuster': '1',
            'user': '(ALARMS_ROLE.user = 1 OR ALARMS_ROLE.user is NULL)',
            'server': '(ALARMS_ROLE.server = 1 OR ALARMS_ROLE.server is NULL)'
        }[role]


class BlockVersionsDB:
    __slots__ = ['_connection']

    def __init__(self, name: str):
        self._connection = sqlite3.connect(name)
        self._configure_db()

    def _configure_db(self):
        cursor = self._connection.cursor()

        cursor.execute(
            "CREATE TABLE IF NOT EXISTS updates (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "previous_version TEXT, " +
            "new_version TEXT, " +
            "alarm_aid INTEGER NOT NULL" +
            ")"
        )

        self._connection.commit()

    def add_alarm(self, aid: int, value: str, end_value: str):
        cursor = self._connection.cursor()
        cursor.execute(f"INSERT INTO updates (previous_version, new_version, alarm_aid) VALUES ('{value}', '{end_value}', {aid})")
        self._connection.commit()

    def get_alarm_by_aid(self, aid: int):
        cursor = self._connection.cursor()

        sql = f"SELECT previous_version, new_version FROM updates WHERE alarm_aid = {aid}"
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[0] if result else ('...', '...')
